/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Objet;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.io.*; 
import jxl.*; 
import jxl.write.*; 


/**
 *
 * @author zouafr
 */
public class QueryBD {
    

    /** Paramètre de connection à la base */
    
    String nomDriver = "oracle.jdbc.driver.OracleDriver";
    
    String url="jdbc:oracle:thin:@miage03.dmiage.u-paris10.fr:1521:miage";
    String user="zouafr"; 
    String mdp="apprentis2012pw";
    Connection cx; 
    
    
    
    public QueryBD() 
    {
        try
        {
            Class.forName(nomDriver);
        }
        catch(ClassNotFoundException ex)
        {
            System.out.println("La classe "+nomDriver+" n'a pu etre trouvée");
            ex.printStackTrace();
        }
        
        try
        {    
            cx = DriverManager.getConnection(url,user,mdp);
        }
        catch(SQLException ex)
        {
            System.err.println("Echec de connexion a la base");
            System.exit(1);
        }
    }
    
    public Hashtable listeEtudiants()
    {
        Hashtable<String,String> listEtudiants= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM Etudiant");

            while(rs.next())
            {
                listEtudiants.put(rs.getString("login"),rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des etudiants");
            System.exit(1);
        }
        return listEtudiants;
    }
    
    public Hashtable listeEnseignant()
    {
        Hashtable<String,String> listEnseignant= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM Enseignant");
            
            while(rs.next())
            {
                listEnseignant.put(rs.getString("login"),rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des enseignants.");
            System.exit(1);
        }
        return listEnseignant;
    }
    
    public Hashtable listeSecretaire()
    {
        Hashtable<String,String> listSecretaire= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM secretaire");
            
            
            while(rs.next())
            {
                System.out.println("OKOOOOOOOOOOOOOOOOOK");
                
                listSecretaire.put(rs.getString("login"),rs.getString("password"));
                
                System.out.println(rs.getString("login")+""+rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL du secretaire.");
            System.exit(1);
        }
        return listSecretaire;
        
    }
    
    public Etudiant creerEtudiant(String login, String mdp){
        
        Etudiant NewEtu = new Etudiant();
        ArrayList<Note> lesNotes;
        
        try{

            Statement st = cx.createStatement();
            ResultSet rs =  st.executeQuery
                    ("Select * FROM ETUDIANT "
                      + "WHERE login='"+login+"' AND password='"+mdp+"'");
            rs.next();

            NewEtu.setNom(rs.getString("nom"));
            NewEtu.setPrenom(rs.getString("prenom"));
            NewEtu.setNetudiant(rs.getInt("netudiant"));
            NewEtu.setLogin(rs.getString("login"));
            NewEtu.setMdp(rs.getString("password"));

            st.close();
            rs.close();
            
            Statement st2 = cx.createStatement();
            int netudiant = NewEtu.getNetudiant();
            ResultSet rs2 = st2.executeQuery 
                ("SELECT * FROM Note WHERE netudiant='"+netudiant+"'");
            
            lesNotes = new ArrayList<Note>();

            while(rs2.next())
            {
                lesNotes.add(new Note(netudiant, 
                             rs2.getString("module"), 
                             rs2.getInt("note")));
            }
            
            NewEtu.setListNotes(lesNotes);
            
            st2.close();
            rs2.close();           
        }
        catch (SQLException ex) {
            System.err.println(":/ Probleme requete SQL recupération des notes."+ex);
            System.exit(1);
        } 
        return NewEtu;
    }
    
    public Enseignant creerEnseignant(String login, String password)
    {
         Enseignant NewEnseignant= new Enseignant();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM ENSEIGNANT "
                       + "WHERE login='"+login+"'AND password='"+password+"'");
                rs.next();

                NewEnseignant.setNom(rs.getString("nom"));
                NewEnseignant.setPrenom(rs.getString("prenom"));
                NewEnseignant.setNumen(rs.getString("numen"));
                NewEnseignant.setLogin(rs.getString("login"));
                NewEnseignant.setMdp(rs.getString("password"));

                st.close();
                rs.close();

                Statement st2 = cx.createStatement();
                String numen = NewEnseignant.getNumen();
                ResultSet rs2 = st2.executeQuery
                    ("Select nom_module, lib FROM MODULE WHERE numen='"+numen+"'");

                ArrayList<Module> lesModules = new ArrayList<Module>();

                while(rs2.next()){
                
                    lesModules.add(new Module(rs2.getString("nom_module"),
                                numen, 
                                rs2.getString("lib")));
                }
                System.out.println("aie 4");
                NewEnseignant.setListModules(lesModules);
                System.out.println("aie 5");
                 st2.close();
                 rs2.close();
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL recupération modules."+ex);
                System.exit(1);
        }
        return NewEnseignant;
    }
    
    public Secretaire creerSecretaire(String login, String password)
    {
         Secretaire NewSecretaire= new Secretaire();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM Secretaire "
                       + "WHERE login='"+login+"'AND password='"+password+"'");
                rs.next();

                NewSecretaire.setNom(rs.getString("nom"));
                NewSecretaire.setPrenom(rs.getString("prenom"));
                NewSecretaire.setNumAD(rs.getInt("numAD"));
                NewSecretaire.setLogin(rs.getString("login"));
                NewSecretaire.setMdp(rs.getString("password"));

                st.close();
                rs.close();

                Statement st2 = cx.createStatement();
                int numAD = NewSecretaire.getNumAD();
                ResultSet rs2 = st2.executeQuery
                    ("Select * FROM Absence ;");

                ArrayList<Absence> lesAbsences = new ArrayList<Absence>();

                while(rs2.next()){
                
                    lesAbsences.add(new Absence(rs2.getInt("id_absence"),
                                rs2.getString("statut"),
                                rs2.getString("creneau")));
                                        

                }
                System.out.println("aie 4");
                NewSecretaire.setListAbsence(lesAbsences);
                st2.close();
                rs2.close();
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL recupération absence."+ex);
                System.exit(1);
        }
        return NewSecretaire;
    }
    
    /*public ArrayList<Note> lesAbsencesEns(int id_etudiant)
    {
        ArrayList<Note> mesNotes = new ArrayList<Note>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     ("Select * FROM EST_NOTER WHERE identifiant='"+module+"'");

                while(rs1.next())
                {
                    mesNotes.add(new Note(rs1.getInt("netudiant"),
                                          module,
                                          rs1.getInt("note")));
                }
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des notes  par module! :"+ex);
                System.exit(1);
            }
            return mesNotes;
    }
    
    public ArrayList<Note> lesAbsences()
    {
        ArrayList<Note> mesNotes = new ArrayList<Note>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     ("Select * FROM Absence");

                while(rs1.next())
                {
                    mesNotes.add(new Note(rs1.getInt("netudiant"),
                                          ,
                                          rs1.getInt("note")));
                }
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des notes  par module! :"+ex);
                System.exit(1);
            }
            return mesNotes;
    }*/
    
    public class FichierExcel { 

 
     File fichier = new File("D:/NetBeans/fichier_excel.xls") ; 
   
        try{ 

        WritableWorkbook ww = Workbook.createWorkbook(fichier); 

        Sheet wSheet = ww.createSheet("premier", 0); 

        WritableFont arial10font = new WritableFont (WritableFont.ARIAL, 10); 
        WritableCellFormat arial10format = new WritableCellFormat(arial10font); 

        LabelCell label0 = new Label(0, 0, "nom",arial10format ); 
        LabelCell label1 = new Label(1, 0, "prenom",arial10format); 
        LabelCell label2 = new Label(0, 1, "ptit"); 
        LabelCell label3 = new Label(1, 1, "pimousse"); 
     
        wSheet.addCell(label0); 
        wSheet.addCell(label1); 
        wSheet.addCell(label2); 
        wSheet.addCell(label3); 
  
        ww.write(); 
        ww.close(); 
        }catch(IOException e){ 
        }catch(jxl.JXLException er){} 
        } 
} 
    
    
    
    
    /*CREATE TABLE "HOHOUSSE"."CRENEAU" 
   (	"NENSEIGNANT" NUMBER(5,0) NOT NULL ENABLE, 
"NSALLE" NUMBER(5,0) NOT NULL ENABLE, 
"NFORMATION" NUMBER(5,0) NOT NULL ENABLE,  
"NEC" NUMBER(5,0) NOT NULL ENABLE, 
"NTYPE" NUMBER(5,0) NOT NULL ENABLE, 
"DATE_CRENEAU" DATE NOT NULL ENABLE, 
"HEURE_DEBUT" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
"HEURE_FIN" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
* 
* 
CONSTRAINT "PK_CRENEAU" PRIMARY KEY ("NENSEIGNANT", "NFORMATION", "NEC", "NTYPE", "DATE_CRENEAU")

CONSTRAINT "FK_CRENEAU_EC" FOREIGN KEY ("NFORMATION", "NUE", "NEC")
 REFERENCES "HOHOUSSE"."EC" ("NFORMATION", "NUE", "NEC") ENABLE, 
CONSTRAINT "FK_CRENEAU_ENSEIGNANT" FOREIGN KEY ("NENSEIGNANT")
 REFERENCES "HOHOUSSE"."ENSEIGNANT" ("NENSEIGNANT") ENABLE, 
CONSTRAINT "FK_CRENEAU_JOURS" FOREIGN KEY ("DATE_CRENEAU")
 REFERENCES "HOHOUSSE"."JOURS" ("DATE_JOUR") ENABLE, 
CONSTRAINT "FK_CRENEAU_SALLE" FOREIGN KEY ("NSALLE")
 REFERENCES "HOHOUSSE"."SALLE" ("NSALLE") ENABLE, 
CONSTRAINT "FK_CRENEAU_TYPE" FOREIGN KEY ("NTYPE")
 REFERENCES "HOHOUSSE"."TYPET" ("NTYPE") ENABLE, 
CONSTRAINT "FK_CRENEAU_REFERENCE_HEURES" FOREIGN KEY ("HEURE_DEBUT", "HEURE_FIN")
 REFERENCES "HOHOUSSE"."HEURES" ("HEURE_DEBUT", "HEURE_FIN") ENABLE

    
     CREATE TABLE "HOHOUSSE"."ABSENCE" 
   (	"NABSENCE" NUMBER(5,0) NOT NULL ENABLE, 
"NENSEIGNANT" NUMBER(5,0), 
"NSALLE" NUMBER(5,0), 
"NFORMATION" NUMBER(5,0), 
"NUE" NUMBER(5,0), 
"NEC" NUMBER(5,0), 
"NTYPE" NUMBER(5,0), 
"DATE_CRENEAU" DATE, 
"NETUDIANT" NUMBER(5,0), 
"STATUT" CHAR(1 BYTE), 
"JUSTIFICATIF" BFILE, 
CONSTRAINT "PK_ABSENCE" PRIMARY KEY ("NABSENCE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
CONSTRAINT "FK_ABSENCE_REFERENCE_CRENEAU" FOREIGN KEY ("NENSEIGNANT", "NSALLE", "NFORMATION", "NUE", "NEC", "NTYPE", "DATE_CRENEAU")
 REFERENCES "HOHOUSSE"."CRENEAU" ("NENSEIGNANT", "NSALLE", "NFORMATION", "NUE", "NEC", "NTYPE", "DATE_CRENEAU") ENABLE, 
CONSTRAINT "FK_ABSENCE_REFERENCE_ETUDIANT" FOREIGN KEY ("NETUDIANT")
 REFERENCES "HOHOUSSE"."ETUDIANT" ("NETUDIANT") ENABLE
   
*/
    
}
